Skip to main content

Query Logs in AWS Athena

You can easily analyze your logs stored in AWS S3 using AWS Athena. Athena allows you to write SQL-style queries that run directly on your stored IO River logs. Follow the steps below to set up the integration:

1. Set Up Log Destination to AWS S3 Bucket​

Use the provided guide to configure the log destination. Make sure to select Delimited Line JSON as the Log File Format.

2. Create a Table in AWS Athena​

Use the following command to create a table in AWS Athena. This assumes the log files are in Delimited Line JSON format.
Make sure to replace your-aws-s3-bucket with your actual bucket name and path.

CREATE EXTERNAL TABLE IF NOT EXISTS ioriver_logs_table (
  service_uid string,
  service_id string,
  provider string,
  timestamp string,
  response_bytes int,
  status_code int,
  path string,
  query_params string,
  domain string,
  referer string,
  geo_location string,
  geo_location_city string,
  http_version string,
  user_agent string,
  client_ip string,
  is_cached string,
  method string,
  midgress_bytes int,
  origin_bytes int,
  unified_logs_behavior_id string,
  x_forwarded_for string,
  time_taken double,
  status_phrase string,
  cookie string,
  server_ip string,
  client_asn string,
  content_encoding string,
  client_asn_name string,
  detailed_result_type string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'ignore.malformed.json' = 'FALSE',
  'dots.in.keys' = 'FALSE',
  'case.insensitive' = 'TRUE',
  'mapping' = 'TRUE'
)
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://your-aws-s3-bucket/'
TBLPROPERTIES ('classification' = 'json');

3. Run Queries​

Once the table is created, you can run SQL queries against the data stored in your S3 bucket.

Example Query​

SELECT *
FROM ioriver_logs_table
WHERE from_iso8601_timestamp(timestamp)
      BETWEEN TIMESTAMP '2025-07-25 02:00:00' AND TIMESTAMP '2025-07-25 02:15:00'